# -*- coding: utf-8 -*-

__author__ = 'gongqf'

import sqlite3
import re
import json
import time
from datetime import datetime
from gis import app
from contextlib import closing
from flask import request, render_template, jsonify, redirect, url_for, Response, send_file, g, session, make_response
from config import DATABASE
from urllib import quote


@app.template_filter('is_list')
def is_list(value):
    return isinstance(value, list)


def query_db(query, args=(), one=False):
    # print query
    cur = g.db.execute(query, args)
    rv = [dict((cur.description[idx][0], value) for idx, value in enumerate(row)) for row in cur.fetchall()]
    return (rv[0] if rv else None) if one else rv


def request_wants_json():
    best = request.accept_mimetypes.best_match(['application/json', 'text/html'])
    return best == 'application/json' and request.accept_mimetypes[best] > request.accept_mimetypes['text/html']


def del_none_value(d):
    for k in d.keys():
        if d[k] is None:
            d.pop(k)
    return d


def db_insert(tbname, kv):
    def exe_sql(d):
        d = del_none_value(d)
        cols = ','.join(d.keys())
        vals = ','.join(['\'%s\'' % v.replace("'", "''") for v in d.values()])
        sql = 'insert into %s (%s) values(%s)' % (tbname, cols, vals)
        # print sql
        query_db(sql)

    if type(kv) is list:
        for x in kv:
            if type(x) is dict:
                exe_sql(x)
    elif type(kv) is dict:
        exe_sql(kv)


def db_update(tbname, kv, cd):
    def exe_sql(d):
        d = del_none_value(d)
        cols = ['%s=' % k for k in d.keys()]
        vals = ['\'%s\'' % v for v in d.values()]

        cv = []
        for x in xrange(len(d)):
            cv.append('%s%s' % (cols[x], vals[x]))

        setval = ','.join(cv)
        query_db('update %s set %s where %s =\'%s\'' % (tbname, setval, cd[0], cd[1]))

    if type(kv) is list:
        for x in kv:
            if type(x) is dict:
                exe_sql(x)
    elif type(kv) is dict:
        exe_sql(kv)


def sarow_to_dict(rows):
    if type(rows) is tuple or type(rows) is list:
        l = []
        for row in rows:
            d = {}
            for column in row.__table__.columns:
                d[column.name] = getattr(row, column.name)
            l.append(d)
        return l
    else:
        return rows


def to_dict(items, use_none=True, del_key=None):
    '''
    items:  是把request.form获得的immutablemultidict对象用iteritems取得
    use_none:是否把空值('')转换为None返回,默认转换为None
    del_key: 是否删除指定的key,比如多余获取的input值,传入值可以是tuple或list,默认不删除
    '''
    d = {}
    for k, v in items:
        if v:
            d[k] = v
        elif use_none:
            d[k] = None
    if del_key:
        if type(del_key) is tuple or type(del_key) is list:
            for key in del_key:
                d.pop(key, None)
        else:
            d.pop(del_key, None)
    return d


def clean_html(html):
    pattern = '\s+|(&nbsp;)+'
    # pattern='\s+'
    # html = re.sub(pattern, ' ', html)
    # 去掉html标记间的空格
    # pattern='> <'
    # html=re.sub(pattern,'><',html)
    return re.sub(pattern, ' ', html).replace('> <', '><')


def clean_row(row):
    for k in row.keys():
        if row[k] is '':
            row[k] = None
    return row


@app.errorhandler(404)
def internal_error(error):
    return render_template('404.html'), 404


@app.errorhandler(500)
def internal_error(error):
    return render_template('500.html'), 500


@app.route('/')
def index():
    return render_template('index.html')


@app.route('/logout')
def logout():
    return redirect(url_for('index'))


@app.route('/login', methods=['POST'])
def login():
    if request.method == 'POST':
        uuser = request.form['uuser']
        upass = request.form['upass']
        sql = 'select * from t_user where u_user=? and u_pass=?'
        print sql
        login_user = query_db(sql, (uuser, upass), one=True)
        if login_user:
            session['uid'] = login_user['u_id']
            session['name'] = login_user['u_name']
            session['user'] = login_user['u_user']
            return jsonify({"bSuccess": True, "iErr": 0})
        else:
            sql = 'select * from t_user where u_user=?'
            error_user = query_db(sql, (uuser,), one=True)
            if error_user:
                return jsonify({"bSuccess": False, "iErr": 300, "sMsg": u"密码错误"})
            else:
                return jsonify({"bSuccess": False, "iErr": 400, "sMsg": u"未找到用户"})


@app.route('/changepassword', methods=['POST'])
def changepassword():
    if request.method == 'POST':
        uid = session['uid']
        uopass = request.form['uopass']
        unpass = request.form['unpass']
        uqpass = request.form['uqpass']

        if unpass != uqpass:
            return jsonify({"bSuccess": False, "iErr": 301, "sMsg": u"密码不匹配"})

        sql = 'select * from t_user where u_id=?'
        exits_user = query_db(sql, (uid,), one=True)
        if exits_user:
            sql = 'select * from t_user where u_id=? and u_pass=?'
            ok_user = query_db(sql, (uid, uopass), one=True)
            if ok_user:
                sql = 'update t_user set u_pass=? where u_id=?'
                query_db(sql, (unpass, uid))
                g.db.commit()
                return jsonify({"bSuccess": True, "iErr": 0, "sMsg": u"密码修改成功"})
            else:
                return jsonify({"bSuccess": False, "iErr": 302, "sMsg": u"原密码错误"})
        else:
            return jsonify({"bSuccess": False, "iErr": 400, "sMsg": u"未找到用户"})


@app.route('/sysUser', methods=['GET', 'POST'])
def sysUser():
    sql = 'select * from t_user'
    users = query_db(sql)
    return render_template('sysUer.html', users=users)
    # return render_template('sysUser-i.html', users=users)


def execute_sql(tbl, cols='*', w=(), page=1, rows=10, sort=None, order=None):
    # sql_table = tbl
    # where_list = []
    where_expr = ''
    if type(cols) is list or type(cols) is tuple:
        cols = ','.join(cols)

    if w:
        where_expr = 'where %s' % ' and '.join(w)
    raw_sql = 'select %s from %s %s' % (cols, tbl, where_expr)
    print 'raw_sql: %s' % raw_sql
    count_sql = 'select count(*) as total from (%s)' % raw_sql
    print 'count_sql: %s' % count_sql

    if sort and order:
        rows_sql = '%s order by %s %s' % (raw_sql, sort, order)
    if page and rows:
        offset = (page - 1) * rows
        rows_sql = '%s limit %d offset %d' % (rows_sql, rows, offset)
    print 'rows_sql: %s' % rows_sql
    rows = query_db(rows_sql)
    total = query_db(count_sql, one=True)['total']
    return total, rows


def execute_sql_simple(tbl, cols='*', w=()):
    # sql_table = tbl
    # where_list = []
    where_expr = ''
    if type(cols) is list or type(cols) is tuple:
        cols = ','.join(cols)

    if w:
        where_expr = 'where %s' % ' and '.join(w)
    raw_sql = 'select %s from %s %s' % (cols, tbl, where_expr)
    print 'raw_sql: %s' % raw_sql

    # if sort and order:
    rows_sql = '%s order by %s %s' % (raw_sql, 'bm', 'asc')
    print 'rows_sql: %s' % rows_sql
    rows = g.db.execute(rows_sql).fetchall()
    return rows


def safe_request(s, d=''):
    return request.args.get(s, d).replace("'", "''")


@app.route('/sysUser.json', methods=['GET', 'POST'])
def sysUser_json():
    # if request.method == 'GET':
    page = request.args.get('page', 1, type=int)
    rows = request.args.get('rows', 10, type=int)
    sort = safe_request('sort', 'u_id')
    order = safe_request('order', 'desc')
    u_id = request.args.get('u_id', 0, type=int)
    u_user = safe_request('u_user', '')
    u_name = safe_request('u_name', '')
    w = []
    w.append('u_id=%s' % u_id) if u_id else None
    w.append('u_user=\'%s\'' % u_user) if u_user else None
    w.append('u_name=\'%s\'' % u_name) if u_name else None

    total, rows = execute_sql(tbl='t_user', w=tuple(w), page=page, rows=rows, sort=sort, order=order)
    return jsonify(total=total, rows=rows)
    # return render_template('sysUser-i.html', users=users)


@app.route('/gl_tree')
def gl_tree():
    tree = list()
    qys = g.db.execute("select id0,bm,mc from teqy where bm in('HS','JD','JB')").fetchall()
    for qy in qys:
        qy_dict = dict()
        qy_dict['id'] = qy[0]
        qy_dict['text'] = qy[2].decode('gbk')
        qy_dict['state'] = 'open'
        qy_dict['children'] = list()

        jzs = g.db.execute("select id0,bm,mc from tejz where zqybm=:qy", {'qy': qy[1]}).fetchall()
        for jz in jzs:
            jz_dict = dict()
            jz_dict['id'] = jz[0]
            jz_dict['text'] = jz[2].decode('gbk')
            jz_dict['state'] = 'closed'
            jz_dict['children'] = list()

            gggls = g.db.execute(u"select id0,bm,mc from tegl where jzbm=:jz and jb='骨干光缆' order by bm",
                                 {'jz': jz[1]}).fetchall()
            if gggls:
                gggl_dict = dict()
                gggl_dict['text'] = u'骨干光缆'
                gggl_dict['state'] = 'closed'
                gggl_dict['children'] = list()
                for gggl in gggls:
                    gl_dict = dict()
                    gl_dict['text'] = gggl[2].decode('gbk')
                    # gl_dict['text'] += '('+gggl[1]+')'
                    gl_dict['state'] = 'open'
                    gl_dict['attributes'] = dict(url='/gl/%s' % gggl[0])
                    # gl_dict['children'] = list()
                    gggl_dict['children'].append(gl_dict)
                jz_dict['children'].append(gggl_dict)

            hjgls = g.db.execute(u"select id0,bm,mc from tegl where jzbm=:jz and jb='汇聚光缆' order by bm",
                                 {'jz': jz[1]}).fetchall()

            if hjgls:
                hjgl_dict = dict()
                hjgl_dict['text'] = u'汇聚光缆'
                hjgl_dict['state'] = 'closed'
                hjgl_dict['children'] = list()
                for hjgl in hjgls:
                    gl_dict = dict()
                    gl_dict['text'] = hjgl[2].decode('gbk')
                    # gl_dict['text'] += '('+hjgl[1]+')'
                    gl_dict['state'] = 'open'
                    gl_dict['attributes'] = dict(url='/gl/%s' % hjgl[0])
                    # gl_dict['children'] = list()
                    hjgl_dict['children'].append(gl_dict)
                jz_dict['children'].append(hjgl_dict)

            jrgls = g.db.execute(u"select id0,bm,mc from tegl where jzbm=:jz and jb='接入光缆' order by bm",
                                 {'jz': jz[1]}).fetchall()

            if jrgls:
                jrgl_dict = dict()
                jrgl_dict['text'] = u'接入光缆'
                jrgl_dict['state'] = 'closed'
                jrgl_dict['children'] = list()
                for jrgl in jrgls:
                    gl_dict = dict()
                    gl_dict['text'] = jrgl[2].decode('gbk')
                    # gl_dict['text'] += '('+jrgl[1]+')'
                    gl_dict['state'] = 'open'
                    gl_dict['attributes'] = dict(url='/gl/%s' % jrgl[0])
                    # gl_dict['children'] = list()
                    jrgl_dict['children'].append(gl_dict)
                jz_dict['children'].append(jrgl_dict)

            qy_dict['children'].append(jz_dict)

        tree.append(qy_dict)

    return Response(json.dumps(tree), mimetype='application/javascript')


@app.route('/dz_tree')
def dz_tree():
    tree = list()
    qys = g.db.execute("select id0,bm,mc from teqy where bm in('HS','JD','JB')").fetchall()
    for qy in qys:
        qy_dict = dict()
        qy_dict['id'] = qy[0]
        qy_dict['text'] = qy[2].decode('gbk')
        qy_dict['state'] = 'open'
        qy_dict['children'] = list()

        jzs = g.db.execute("select id0,bm,mc from tejz where zqybm=:qy", {'qy': qy[1]}).fetchall()
        for jz in jzs:
            jz_dict = dict()
            jz_dict['id'] = jz[0]
            jz_dict['text'] = jz[2].decode('gbk')
            jz_dict['state'] = 'closed'
            jz_dict['children'] = list()

            gjs = g.db.execute("select id0,bm,mc from tegjjx where jzbm=:jz order by bm", {'jz': jz[1]}).fetchall()
            for gj in gjs:
                gj_dict = dict()
                gj_dict['text'] = gj[2].decode('gbk')
                gj_dict['state'] = 'open'
                gj_dict['attributes'] = dict(url='/gj/%s' % gj[0])

                jz_dict['children'].append(gj_dict)

                gj_obds = g.db.execute("select id0,bm,mc from teobd where ztbm=:gj order by bm",
                                       {'gj': gj[1]}).fetchall()
                if gj_obds:
                    gj_obd_dict = dict()
                    gj_obd_dict['text'] = 'OBD'
                    gj_obd_dict['state'] = 'closed'
                    gj_obd_dict['children'] = list()

                    for obd in gj_obds:
                        obd_dict = dict()
                        obd_dict['text'] = obd[2].decode('gbk')
                        obd_dict['state'] = 'open'
                        obd_dict['attributes'] = dict(url='/obd/%s' % obd[0])
                        # obd_dict['children'] = list()

                        gj_obd_dict['children'].append(obd_dict)

                    gj_dict['children'] = list()
                    gj_dict['children'].append(gj_obd_dict)

            jfs = g.db.execute("select id0,bm,mc from tejf where jzbm=:jz and jflx=1 order by bm",
                               {'jz': jz[1]}).fetchall()
            for jf in jfs:
                jf_dict = dict()
                jf_dict['id'] = jf[0]
                jf_dict['text'] = jf[2].decode('gbk')
                jf_dict['state'] = 'open'
                jf_dict['children'] = list()

                odfs = g.db.execute("select id0,bm,mc from teodf where sysm=0 and jfbm=:jf order by bm",
                                    {'jf': jf[1]}).fetchall()
                if odfs:
                    jf_odf_dict = dict()
                    jf_odf_dict['text'] = 'ODF'
                    jf_odf_dict['state'] = 'open'
                    jf_odf_dict['children'] = list()

                    for odf in odfs:
                        odf_dict = dict()
                        odf_dict['text'] = odf[2].decode('gbk')
                        odf_dict['state'] = 'open'
                        odf_dict['attributes'] = dict(url='/odf/%s' % odf[0])
                        # odf_dict['children'] = list()

                        jf_odf_dict['children'].append(odf_dict)

                    jf_dict['children'].append(jf_odf_dict)

                jf_obds = g.db.execute("select id0,bm,mc from teobd where ztbm=:jf order by bm",
                                       {'jf': jf[1]}).fetchall()
                if jf_obds:
                    jf_obd_dict = dict()
                    jf_obd_dict['text'] = 'OBD'
                    jf_obd_dict['state'] = 'closed'
                    jf_obd_dict['children'] = list()

                    for obd in jf_obds:
                        obd_dict = dict()
                        obd_dict['text'] = obd[2].decode('gbk')
                        obd_dict['state'] = 'open'
                        obd_dict['attributes'] = dict(url='/obd/%s' % obd[0])
                        # obd_dict['children'] = list()

                        jf_obd_dict['children'].append(obd_dict)

                    jf_dict['children'].append(jf_obd_dict)

                jf_olts = g.db.execute("select id0,bm,mc from teolt where jfbm=:jf order by bm",
                                       {'jf': jf[1]}).fetchall()
                if jf_olts:
                    jf_olt_dict = dict()
                    jf_olt_dict['text'] = 'OLT'
                    jf_olt_dict['state'] = 'closed'
                    jf_olt_dict['children'] = list()

                    for olt in jf_olts:
                        olt_dict = dict()
                        olt_dict['text'] = olt[2].decode('gbk')
                        olt_dict['state'] = 'open'
                        olt_dict['attributes'] = dict(url='/olt/%s' % olt[0])
                        # olt_dict['children'] = list()

                        jf_olt_dict['children'].append(olt_dict)

                    jf_dict['children'].append(jf_olt_dict)

                jz_dict['children'].append(jf_dict)

            qy_dict['children'].append(jz_dict)

        tree.append(qy_dict)

    return Response(json.dumps(tree), mimetype='application/javascript')


def gbk_to_utf8(rs):
    if type(rs) == list or type(rs) == tuple:
        rows = list()
        for r in rs:
            if type(r) == list or type(r) == tuple:
                row = list(r)
                for i, e in enumerate(r):
                    if type(r[i]) == str:
                        row[i] = r[i].decode('gbk')
                        if re.search(u',|，', row[i]):
                            bz = row[i]
                            row[i] = re.split(u',|，', bz)
                            # print rv[head[idx]]
            elif type(r) == str:
                row = r.decode('gbk')
            else:
                row = r
            rows.append(row)
        return rows


def combin_dict(head, values):
    # return dict(zip(head, values.decode('gbk') if type(values) == str else values))
    if type(values) == tuple:
        rv = dict()
        for x in range(len(head)):
            if type(values[x]) == str:
                rv[head[x]] = values[x].decode('gbk')
            else:
                rv[head[x]] = values[x]
        return rv
    elif type(values) == list:
        rvs = list()
        for value in values:
            rv = dict()
            for idx in range(len(value)):
                if type(value[idx]) == str:
                    v = value[idx].decode('gbk')
                    if re.search(u',|，', v):
                        # print value[idx].decode('gbk')
                        rv[head[idx]] = re.split(u',|，', v)
                        # print rv[head[idx]]
                    else:
                        rv[head[idx]] = v
                        # print rv[head[idx]]
                else:
                    rv[head[idx]] = value[idx]
            rvs.append(rv)
        return rvs


def get_dev_table_name(devtype):
    return {'1': 'teodf', '2': 'tegjjx', '9': 'teobd', '11': 'teolt'}[str(devtype)]


def get_gj_symbol(idx):
    return (
    'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W',
    'X', 'Y', 'Z', 'AA')[int(idx) - 1]


def get_odf_jh(bm):
    odf = re.search('ODF.+', bm).group()
    return str(int(''.join(re.findall('\d', odf))))
    # return str(int(re.search('\d.+',odf_bm).group()))


def get_olt_jh(bm):
    olt = re.search('OLT.+', bm).group()
    return str(int(''.join(re.findall('\d', olt))))
    # return str(int(re.search('\d.+',odf_bm).group()))


def dz_tj_info(devtype, dzs):
    pass


def get_dz_pw(devtype, devid):
    # 所有a端端子盘位
    adz_pw = get_dev_pw(devtype=devtype, devid=devid, out=False)
    # print adz_pw
    bdev_sql = 'SELECT bdevlx, bdevid FROM (SELECT cdevlx bdevlx, cdevid bdevid FROM tegjsbjmp rjmp WHERE rdevid = :devid AND rdevlx = :devtype UNION ALL SELECT rdevlx bdevlx, rdevid bdevid FROM tegjsbjmp cjmp WHERE cdevid = :devid AND cdevlx = :devtype) WHERE (bdevlx <> :devtype AND bdevid <> :devid) or (bdevlx = :devtype AND bdevid <> :devid) GROUP BY bdevlx, bdevid'

    bdevs = g.db.execute(bdev_sql, {'devtype': devtype, 'devid': devid}).fetchall()
    # print bdevs

    bdz_pw = dict()
    for bdev in bdevs:
        bdz_pw = dict(bdz_pw, **get_dev_pw(*bdev, out=False))
    # print bdz_pw

    dz_pw = dict(adz_pw, **(bdz_pw))
    return dz_pw


@app.route('/get_dev_pw/<int:devtype>/<int:devid>', methods=['GET'])
def get_dev_pw(devtype, devid, out=True):
    if devtype == 11:
        return get_olt_pw(devid, out)
    if devtype == 9:
        return get_obd_pw(devid, out)
    if devtype == 2:
        return get_gj_pw(devid, out)
    if devtype == 1:
        return get_odf_pw(devid, out)


@app.route('/get_gj_pw/<int:devid>', methods=['GET'])
def get_gj_pw(devid=None, out=False):
    s_time = time.time()
    # head = ['id0', 'devid', 'mkrow', 'mkcol', 'dzrow', 'dzcol', 'dz', 'zt', 'bz']
    # sql = 'select %s from tegjsbdz dz where dz.devid = :devid order by dz.mkcol, dz.mkrow, dz.dzcol' % ','.join(head)
    # dzs = combin_dict(head, g.db.execute(sql, {'devid': devid}).fetchall())
    # rows = g.db.execute('select mkcol,max(mkrow) from tegjsbdz dz where devid = :devid group by mkcol ', {'devid': devid}).fetchall()
    # print rows
    # mks = dict((row[0], row[1]) for row in rows)
    # print mks
    # for dz in dzs:
    # dz['pw']='%s-%s-%s'%(get_gj_symbol(dz['mkcol']), get_gj_symbol(mks[dz['mkcol']]-dz['mkrow']+1)  ,dz['dzcol'])
    head = ['id0', 'mkrow', 'mkcol', 'dzcol']
    sql = 'select %s from tegjsbdz dz where dz.devid = :devid and devtype=2 order by dz.mkcol, dz.mkrow, dz.dzcol' % ','.join(
        head)
    dzs = g.db.execute(sql, {'devid': devid}).fetchall()
    rows = g.db.execute('select mkcol,max(mkrow) from tegjsbdz dz where devid = :devid and devtype=2 group by mkcol ',
                        {'devid': devid}).fetchall()
    print rows
    mks = dict((row[0], row[1]) for row in rows)
    print mks
    dz_pw = dict()
    for dz in dzs:
        dz_pw[dz[0]] = '%s-%s-%s' % (get_gj_symbol(dz[2]), get_gj_symbol(mks[dz[2]] - dz[1] + 1), dz[3])
    print time.time() - s_time
    if out:
        return jsonify(dz_pw=dz_pw)
    else:
        return dz_pw


@app.route('/get_obd_pw/<int:devid>', methods=['GET'])
def get_obd_pw(devid=None, out=False):
    s_time = time.time()
    # 用编码显示
    sql = "SELECT dz.id0 id0 ,substr(obd.bm,instr(obd.bm,'OBD')+3,LENGTH(obd.bm)-instr(obd.bm,'OBD')-3+1)||'/'||dz.dz pw FROM tegjsbdz dz,teobd obd where dz.devid=:devid and dz.devid=obd.id0"
    # 用名称显示
    sql = u"SELECT dz.id0 id0 ,substr(obd.mc,instr(obd.mc,'分光器')+3,LENGTH(obd.mc)-instr(obd.mc,'分光器')-3+1)||'/'||dz.dz pw FROM tegjsbdz dz,teobd obd where dz.devid=:devid and dz.devid=obd.id0"
    rows = g.db.execute(sql, {'devid': devid}).fetchall()
    dz_pw = dict((row[0], row[1]) for row in rows)
    print time.time() - s_time
    if out:
        return jsonify(dz_pw=dz_pw)
    else:
        return dz_pw


@app.route('/get_odf_pw/<int:devid>', methods=['GET'])
def get_odf_pw(devid=None, out=False):
    s_time = time.time()
    sql = 'select bm from teodf where id0=:devid'
    jh = get_odf_jh(g.db.execute(sql, {'devid': devid}).fetchone()[0])
    head = ['id0', 'mkrow', 'dzrow', 'dzcol']
    # select dz.id0 id0, kh, dzrow, dzcol from tegjsbdz dz left join teodm odm on dz.mkrow=odm.wlkh and dz.devid=odm.devid and dz.devtype=odm.devtype where dz.devid = 17413 and dz.devtype=1 order by dz.mkrow, dz.dzrow, dz.dzcol
    sql = 'select dz.id0 id0, kh, dzrow, dzcol from tegjsbdz dz left join teodm odm on dz.mkrow=odm.wlkh and dz.devid=odm.devid and dz.devtype=odm.devtype where dz.devid = :devid and dz.devtype=1 order by dz.mkrow, dz.dzrow, dz.dzcol'
    dzs = g.db.execute(sql, {'devid': devid}).fetchall()
    dz_pw = dict()
    for dz in dzs:
        dz_pw[dz[0]] = '%s-%s-%s-%s' % (jh, dz[1], dz[2], dz[3])
    print time.time() - s_time
    if out:
        return jsonify(dz_pw=dz_pw)
    else:
        return dz_pw


@app.route('/get_olt_pw/<int:devid>', methods=['GET'])
def get_olt_pw(devid=None, out=False):
    # 未完成
    s_time = time.time()
    sql = 'select bm from teolt where id0=:devid'
    jh = get_olt_jh(g.db.execute(sql, {'devid': devid}).fetchone()[0])
    sql = "SELECT dz.id0,kh,dzb.bm,dz.bm FROM tegjsbdz dz left join tegldzb dzb on dz.devtype=dzb.devtype and dz.devid=dzb.devid and dz.mkrow=dzb.mkhh and dz.mkcol=dzb.mklh and dz.ssjk=dzb.ssjk left join teodm odm on dzb.devtype=odm.devtype and dzb.devid=odm.devid and dzb.ssjk=odm.id0 where dz.devid=:devid and dz.devtype=11 order by mkrow,mkcol,dzrow,dzcol"
    dzs = g.db.execute(sql, {'devid': devid}).fetchall()
    dz_pw = dict()
    for dz in dzs:
        dz_pw[dz[0]] = '%s-%s/%s/%s' % (jh, dz[1], dz[2], dz[3])
    print time.time() - s_time
    if out:
        return jsonify(dz_pw=dz_pw)
    else:
        return dz_pw


@app.route('/gj/<int:id0>', methods=['GET'])
def gj(id0=None):
    export = request.args.get('export')

    dz_pw = get_dz_pw(devtype=2, devid=id0)

    dzs_sql = 'select id0,devid,mkrow,mkcol,dzrow,dzcol,dz,zt,bz,bdevtype,bdevid,bdz from tegjsbdz dz left join  (SELECT rjmp.id0 adz, cdz.devid bdevid, cdz.devtype bdevtype, cdz.id0 bdz FROM (SELECT rdz.id0, jmp.rdz, jmp.rdevlx, jmp.rdevid, jmp.cdz, jmp.cdevlx, jmp.cdevid FROM tegjsbjmp jmp left join tegjsbdz rdz ON rdz.devid = jmp.rdevid AND rdz.devtype = jmp.rdevlx AND rdz.dz = jmp.rdz WHERE jmp.rdevid = :id0 AND jmp.rdevlx = 2) rjmp left join tegjsbdz cdz ON cdz.devid = rjmp.cdevid AND cdz.devtype = rjmp.cdevlx AND cdz.dz = rjmp.cdz UNION ALL SELECT cjmp.id0 adz, rdz.devid bdevid, rdz.devtype bdevtype, rdz.id0 bdz FROM ( SELECT cdz.id0, jmp.cdz, jmp.cdevlx, jmp.cdevid, jmp.rdz, jmp.rdevlx, jmp.rdevid FROM tegjsbjmp jmp left join tegjsbdz cdz ON cdz.devid = jmp.cdevid AND cdz .devtype = jmp.cdevlx AND cdz.dz = jmp.cdz WHERE jmp.cdevid = :id0 AND jmp.cdevlx = 2) cjmp left join tegjsbdz rdz ON rdz.devid = cjmp.rdevid AND rdz.devtype = cjmp.rdevlx AND rdz.dz = cjmp.rdz) b on dz.id0=b.adz where devtype=2 and devid=:id0 order by mkcol,mkrow,dzcol'

    dzs = gbk_to_utf8(g.db.execute(dzs_sql, {'id0': id0}).fetchall())
    # print dzs

    mbs_sql = 'SELECT mkcol,max(mkrow),max(dzcol) FROM tegjsbdz where devid=:id0 and devtype=2 group by mkcol order by mkcol'
    mbs = g.db.execute(mbs_sql, {'id0': id0}).fetchall()

    tables = list()

    for mb in mbs:
        table = dict()
        table['table'] = mb[0]
        table['max_row'] = mb[1]
        table['max_col'] = mb[2]
        trs = list()
        for row in range(1, mb[1] + 1):
            tr = dict()
            tr['tr'] = row
            tds = list()
            for col in range(1, mb[2] + 1):
                for dz in dzs:

                    if dz[3] == mb[0] and dz[2] == row and dz[5] == col:
                        tds.append(dz)
            tr['tds'] = tds
            trs.append(tr)
        table['trs'] = trs
        tables.append(table)

    gl_cds = g.db.execute(
        "select dz, mc||'/'||qx qx, gl.jb from tegjsbgl cd left join tegld gld on cd.gldid=gld.id0 left join tegl gl on gld.glid=gl.id0 where cd.devid=:id0 and cd.devtype=2",
        {'id0': id0}).fetchall()
    # print gl_cds[0]
    # print 'gl_cds:',time.time()-s_time
    gljb = {u'接入光缆': 'gl-jb-jr', u'汇聚光缆': 'gl-jb-hj', u'骨干光缆': 'gl-jb-gg'}
    gl_cd = dict((x[0], x[1].decode('gbk')) for x in gl_cds)
    gl_jb = dict((x[0], gljb[x[2].decode('gbk')]) for x in gl_cds)


    # return jsonify(tables=tables)


    if export:
        gjmc = g.db.execute('select mc from tegjjx where id0=:id0', {'id0': id0}).fetchone()[0].decode('gbk').encode(
            'utf-8')
        resp = make_response(render_template('gj.html', tables=tables, dz_pw=dz_pw, gl_jb=gl_jb, gl_cd=gl_cd))
        resp.headers['Content-Disposition'] = "attachment; filename='%s.%s'" % (quote(gjmc), export)
        resp.headers['Content-Type'] = "application/octet-stream"
        return resp
    else:
        return render_template('gj.html', tables=tables, dz_pw=dz_pw, gl_jb=gl_jb, gl_cd=gl_cd)


@app.route('/olt/<int:id0>', methods=['GET'])
def olt(id0=None):
    s_time = time.time()
    export = request.args.get('export')

    dz_pw = get_dz_pw(devtype=11, devid=id0)

    tables = list()
    print time.time() - s_time
    # return jsonify(tables=tables)
    if export:
        oltmc = g.db.execute('SELECT mc FROM teolt where id0=:id0', {'id0': id0}).fetchone()[0].decode('gbk').encode(
            'utf-8')
        resp = make_response(render_template('olt.html', tables=tables, dz_pw=dz_pw))
        resp.headers['Content-Disposition'] = "attachment; filename='%s.%s'" % (quote(oltmc), export)
        resp.headers['Content-Type'] = "application/octet-stream"
        return resp
    else:
        return render_template('olt.html', tables=tables, dz_pw=dz_pw)


@app.route('/odf/<int:id0>', methods=['GET'])
def odf(id0=None):
    s_time = time.time()
    export = request.args.get('export')

    dz_pw = get_dz_pw(devtype=1, devid=id0)

    dzs_sql = 'SELECT id0,devid,mkrow,mkcol,dzrow,dzcol,dz,zt,bz,bdevtype,bdevid,bdz FROM tegjsbdz dz left join (SELECT rjmp.id0 adz,cdz.devid bdevid,cdz.devtype bdevtype,cdz.id0 bdz FROM (SELECT rdz.id0,jmp.rdz,jmp.rdevlx,jmp.rdevid,jmp.cdz,jmp.cdevlx,jmp.cdevid,jmp.ckh FROM tegjsbjmp jmp left join tegjsbdz rdz ON rdz.devid = jmp.rdevid AND rdz.devtype = jmp.rdevlx AND rdz.dz = jmp.rdz AND (SELECT kh FROM teodm WHERE devid = :id0 AND devtype = 1 AND wlkh = rdz.mkrow) = jmp.rkh WHERE jmp.rdevid = :id0 AND jmp.rdevlx = 1 AND jmp.cdevlx = 1) rjmp left join tegjsbdz cdz ON cdz.devid = rjmp.cdevid AND cdz.devtype = rjmp.cdevlx AND cdz.dz = rjmp.cdz AND (SELECT kh FROM teodm WHERE devid = rjmp.cdevid AND devtype = 1 AND wlkh = cdz.mkrow) = rjmp.ckh UNION ALL SELECT rjmp.id0 adz,cdz.devid bdevid,cdz.devtype bdevtype,cdz.id0 bdz FROM (SELECT rdz.id0,jmp.rdz,jmp.rdevlx,jmp.rdevid,jmp.cdz,jmp.cdevlx,jmp.cdevid,jmp.ckh FROM tegjsbjmp jmp left join tegjsbdz rdz ON rdz.devid = jmp.rdevid AND rdz.devtype = jmp.rdevlx AND rdz.dz = jmp.rdz AND (SELECT kh FROM teodm WHERE devid = :id0 AND devtype = 1 AND wlkh = rdz.mkrow) = jmp.rkh WHERE jmp.rdevid = :id0 AND jmp.rdevlx = 1 AND jmp.cdevlx <> 1) rjmp left join tegjsbdz cdz ON cdz.devid = rjmp.cdevid AND cdz.devtype = rjmp.cdevlx AND cdz.dz = rjmp.cdz UNION ALL SELECT cjmp.id0 adz,rdz.devid bdevid,rdz.devtype bdevtype,rdz.id0 bdz FROM (SELECT cdz.id0,jmp.cdz,jmp.cdevlx,jmp.cdevid,jmp.rdz,jmp.rdevlx,jmp.rdevid,jmp.rkh FROM tegjsbjmp jmp left join tegjsbdz cdz ON cdz.devid = jmp.cdevid AND cdz .devtype = jmp.cdevlx AND cdz.dz = jmp.cdz AND (SELECT kh FROM teodm WHERE devid = :id0 AND devtype = 1 AND wlkh = cdz.mkrow) = jmp.ckh WHERE jmp.cdevid = :id0 AND jmp.cdevlx = 1 AND jmp.rdevlx = 1) cjmp left join tegjsbdz rdz ON rdz.devid = cjmp.rdevid AND rdz.devtype = cjmp.rdevlx AND rdz.dz = cjmp.rdz AND (SELECT kh FROM teodm WHERE devid = cjmp.rdevid AND devtype = 1 AND wlkh = rdz.mkrow) = cjmp.rkh UNION ALL SELECT cjmp.id0 adz,rdz.devid bdevid,rdz.devtype bdevtype,rdz.id0 bdz FROM (SELECT cdz.id0,jmp.cdz,jmp.cdevlx,jmp.cdevid,jmp.rdz,jmp.rdevlx,jmp.rdevid,jmp.rkh FROM tegjsbjmp jmp left join tegjsbdz cdz ON cdz.devid = jmp.cdevid AND cdz .devtype = jmp.cdevlx AND cdz.dz = jmp.cdz AND (SELECT kh FROM teodm WHERE devid = :id0 AND devtype = 1 AND wlkh = cdz.mkrow) = jmp.ckh WHERE jmp.cdevid = :id0 AND jmp.cdevlx = 1 AND jmp.rdevlx <> 1) cjmp left join tegjsbdz rdz ON rdz.devid = cjmp.rdevid AND rdz.devtype = cjmp.rdevlx AND rdz.dz = cjmp.rdz) b ON dz.id0 = b.adz WHERE devtype = 1 AND devid = :id0 ORDER BY mkcol,mkrow,dzrow,dzcol '

    dzs = gbk_to_utf8(g.db.execute(dzs_sql, {'id0': id0}).fetchall())
    # print dzs


    # kh，wlkh的引用会不错换错？看结果是对的，修改gis里的框号，修改的是kh这个字段。
    mbs_sql = 'SELECT kh,wlkh,kdys,dybs,bdzs FROM teodm where devtype=1 and devid=:id0 order by wlkh'
    mbs = g.db.execute(mbs_sql, {'id0': id0}).fetchall()

    tables = list()

    for mb in mbs:
        table = dict()
        table['table'] = mb[0]
        table['max_row'] = mb[3]
        table['max_col'] = mb[4]
        trs = list()
        for row in range(1, mb[3] + 1):
            tr = dict()
            tr['tr'] = row
            tds = list()
            for col in range(1, mb[4] + 1):
                for dz in dzs:

                    if dz[2] == mb[1] and dz[4] == row and dz[5] == col:
                        tds.append(dz)
            tr['tds'] = tds
            trs.append(tr)
        table['trs'] = trs
        tables.append(table)

    gl_cds = g.db.execute(
        "select wlkh, dz, mc||'/'||qx qx, gl.jb from tegjsbgl cd left join teodm odm on cd.kh=odm.kh and cd.devid=odm.devid and cd.devtype=odm.devtype left join tegld gld on cd.gldid=gld.id0 left join tegl gl on gld.glid=gl.id0 where cd.devid=:id0 and cd.devtype=1",
        {'id0': id0}).fetchall()
    # print gl_cds[0]
    gljb = {u'接入光缆': 'gl-jb-jr', u'汇聚光缆': 'gl-jb-hj', u'骨干光缆': 'gl-jb-gg'}
    gl_jb = dict(('%s-%s' % (x[0], x[1].decode('gbk')), gljb[x[3].decode('gbk')]) for x in gl_cds)
    gl_cd = dict(('%s-%s' % (x[0], x[1].decode('gbk')), x[2].decode('gbk')) for x in gl_cds)

    print time.time() - s_time
    # return jsonify(tables=tables)
    if export:
        odfmc = g.db.execute('SELECT mc FROM teodf where id0=:id0', {'id0': id0}).fetchone()[0].decode('gbk').encode(
            'utf-8')
        resp = make_response(render_template('odf.html', tables=tables, dz_pw=dz_pw, gl_jb=gl_jb, gl_cd=gl_cd))
        resp.headers['Content-Disposition'] = "attachment; filename='%s.%s'" % (quote(odfmc), export)
        resp.headers['Content-Type'] = "application/octet-stream"
        return resp
    else:
        return render_template('odf.html', tables=tables, dz_pw=dz_pw, gl_jb=gl_jb, gl_cd=gl_cd)


@app.route('/obd/<int:id0>', methods=['GET'])
def obd(id0=None):
    export = request.args.get('export')
    obd_head = ('bm', 'mc')
    obd_dev = combin_dict(list(obd_head), g.db.execute('select %s from teobd where id0 = :id0' % ','.join(obd_head),
                                                       {'id0': id0}).fetchone())

    dz_head = ('dz.id0', 'dz.devid', 'dz.mkrow', 'dz.mkcol', 'dz.dzrow', 'dz.dzcol', 'dz.dz', 'dz.zt', 'dz.bz')
    max_mkrow, max_dzcol = g.db.execute(
        'select max(mkrow),max(dzcol) from tegjsbdz dz where devid = :id0 and devtype=9', {'id0': id0}).fetchone()
    print max_mkrow, max_dzcol
    mbs = list()
    # dzs = list()
    for mkrow in range(1, max_mkrow + 1):
        mb = dict()
        mb['mk_row'] = mkrow
        mb['mk'] = combin_dict(list(dz_head), g.db.execute(
            'select %s from tegjsbdz dz where dz.devid = :id0 and devtype=9 and dz.mkrow=:mkrow order by dz.dzcol' % (
                ','.join(dz_head)),
            {'id0': id0, 'mkrow': mkrow}).fetchall())
        print mb
        mbs.append(mb)
    if export:
        resp = make_response(render_template('obd.html', devid=id0, mbs=mbs, obd_dev=obd_dev))
        resp.headers['Content-Disposition'] = 'attachment; filename=obd.%s' % export
        resp.headers['Content-Type'] = "application/octet-stream"
        return resp
    else:
        return render_template('obd.html', devid=id0, mbs=mbs, obd_dev=obd_dev)


@app.route('/gl/<int:id0>', methods=['GET'])
def gl(id0=None):
    export = request.args.get('export')
    gl_head = ('bm', 'mc', 'jzbm', 'jb', 'cd', 'bz')
    gl = combin_dict(list(gl_head),
                     g.db.execute('select %s from tegl where id0=:id0' % ','.join(gl_head), {'id0': id0}).fetchone())

    gld_head = ('id0', 'bm', 'mc', 'gldxxs', 'qslx', 'qsbm', 'zzlx', 'zzbm', 'glcd')

    glds = combin_dict(list(gld_head),
                       g.db.execute('select %s from tegld where glid=:id0 order by bm' % ','.join(gld_head),
                                    {'id0': id0}).fetchall())
    if export:
        resp = make_response(render_template('gl.html', glid=id0, gl=gl, glds=glds))
        resp.headers['Content-Disposition'] = 'attachment; filename=gl.%s' % export
        resp.headers['Content-Type'] = "application/octet-stream"
        return resp
    else:
        return render_template('gl.html', glid=id0, gl=gl, glds=glds)


@app.route('/gld.json', methods=['GET'])
def gld_json():
    glid = request.args.get('glid', 0, type=int)
    w = []
    w.append('glid=%s' % glid) if glid else None

    rows = execute_sql_simple(tbl='tegld', cols=('id0', 'bm', 'glcd'), w=tuple(w))
    return jsonify(rows=rows)
